MM – Mail merge

1 Introduction[//]

Mailmerge processing is based on the idea of automatically merging a main document in which “source fields” are embedded; linked to the main document is a data source which contains the source data. When the mailmerge process is executed, the word processor will replace the source field codes by the data from the data source repeatedly to build the output pages.

The main document therefore describes fixed text and the layout of the source fields. The idea then is that Vubis creates the data in the specified data source - but the library may define its own format for how these fields are laid out.

Expanding on this, for any specific type of notice, Vubis will define ALL the possible data fields which may be relevant for that type of notice. The notice generation processing will populate all these fields. When the library staff user (the user from here on) comes to layout their notices, then they can select which fields are relevant to them. From the point of view of the application software, no options need to be offered – all the data is made available and it is up to the user to select what they want.

For example, for bibliographic data, we might make title, author, ISBN, publication date, edition, publisher, number of pages, available, but on the overdue notice only title and ISBN might be output.

1.1 Environment[//]

Mailmerge processing is available as a standard function of all commercial or opensource word processing packages. The power of such word processors is opened up from Vubis by creating the source data on the host server, and invoking the actual mailmerge processing by means of a “macro” command sent to the relevant WP software.

The wordprocessing software will be installed on dedicated PCs. These PCs will play the role of a print processor. The principles of the design and interaction of Vubis and the wordprocessing package should be applicable to most major WP systems, but initially support is available to Microsoft Word and to OpenOffice (or SunOffice) as the main commercial and open source word processing systems.

How this actually works in some detail comprises the main part of this document. The first sections address the general principles, and later sections describe the details for each type of notice for which this technique is available.

1.2 Comparing processes[//]

It is NOT a requirement to use mail merge for all standard, existing notices.

The regular processing basically involves three steps

1. The selection of the records for which the notice is required. This creates a “workfile” holding key information, from which the actual output can then be generated.

2. The independent step of actually creating the notices – creates the actual printed form (and marks the workfile as having been printed).

3. The dispatch of this printed form to the Vubis client from which this “text” is sent to the required printer.

With mailmerge these three steps remain. Step 1 remains unchanged, but step 2 is the generation of the data source, and step 3 is the process of initiating the word processor / mailmerge steps.

Existing notice types remain available to the plain text style or optionally converted to use the mailmerge output method. Mailmerge output allows for the printing of a full Unicode character set which is not available with the plain text style.

HOWEVER, for new features within the application, the older style of printing may no longer be offered. For example, for the Stack Request module, the Interlibrary Loan module and for notices informing of expiring membership, only the mail merge technique will be made available for notice output.

1.3 Technical overview[//]

This section describes in more detail how the mailmerge processing works. It explains how the processing is managed once the wordprocessor is started, and the functions that are common to all environments.

1.3.1 Data sources[//]

When a user requires a set of notices to be printed, the system processes the “workfile” created by the selection phase to build the datasource for the mailmerge processing. This datasource is specific to each type of notice but in all cases comprises an SQL table inside the main database, which can be accessed using ODBC from the Wordprocessor.

1.3.2 Control tables[//]

The system then creates entries in the SQL tables VMMMainSQL and VMMEntriesSQL. These tables hold some crucial information about the way the mailmerge output is to be created, and which can then be “passed” to the Wordprocessor; specifically

· Details of the data source

· The name of the main document (the “template”)

· The printer to which the output should be sent

· Whether the output should also be saved on disk

· Whether the results should be delivered by email

1.3.3 The Print Processor and The Word processing macro[//]

One or more PCs are configured to start as “print processors”. Usually this task will be part of the startup of the Workstation. The print processor executes a mailmerge macro. Basically this means that the Wordprocessor (Microsoft Word or Open Source) will run permanently on that workstation.

When a printing is initiated from Vubis the required information will be populated in the specific SQL tables (VMMMainSQL, VMMEntriesSQL etc). The print processor will run a macro that will poll in a continuous loop the VMMMainSQL for prints to be done, sleeping for a few seconds if nothing found.

It is this particular macro which then does all the “work” involved. The macro carries out the following tasks based on the contents of the rows found in the tables: VMMMainSQL and VMMEntriesSQL.

1. It opens the “main document”

2. Attaches the datasource to the document

3. Executes the mailmerge processing.

4. Ensures that the character set is converted to Unicode

5. Carries out some special processing for formatting notices which are comprised of “lists” (for example, overdues).

6. Sends the results to a printer (if required)

7. Saves the results in a file (if appropriate)

8. Emails the results (if appropriate)

9. Closes all files

1.4 Main documents and data sources[//]

A few examples to illustrate how the interface works.

These examples used MSWord,– it does not mean that MSWord is the only or preferred software for handling this.

The system is offered with a number of predefined main documents which may then be amended to meet a site's own requirements including layout, fonts, styles, fixed texts, graphics etc.

The fields in grey are the field names from the datasource.

For a particular type of notice, a pre-defined list of available fields is offered allowing custom design and layout within the document.

1.4.1 More on data sources[//]

This section further refines the general principles on data sources. The contents and structure of the data sources themselves are specific to each type of notice – “expiry date”, for example, is only relevant to the membership expiry notice but not relevant for stack request or Interlibrary Loan notices.

The full extended character set for Unicode/UTF8 formats is supported.

The currenty defined "views" are:

· ASRRequestView

· ASRBatchView

· ExpiringMembershipView

· BorrowerLabelView

· SerialsLabelView

· Notices.OverduesView

· Notices.ReservationsView

· Notices.SerialsBindingView

· IBLRequestView

· IBLInvoicesView

· IBLBorrowerNoticeView

Each has a master template defined in the Document Properties / Custom settings e.g.

The specific table is defined in "VubisView" supplied by Infor and should NOT be changed. ODBCSource and VubisDatabase MAY need to be adjusted.

2 System settings for notices[//]

The following sections describe how the system is configured to use mailmerge notices. Different notice types require different settings For mailmerge processing, the concept of “notice sets" allows the system administrator to link a notice print location / borrower category to a “set” of layouts. This makes it more convenient to share such layouts between different borrower categories and/or locations.

2.1 Notice sets[//]

A notice set simply defines the names of the master documents which relate to each type of notice. Notice sets are defined per circulation metainstitution. These master documents are defined independently, can be shared and are linked to the various functions.

Notice sets are defined in AFO 483 – Mailmerge parameters – Notice sets (for borrower related notices such as overdues, reservations, expiring membership etc.), in AFO 618 – Stack request notice sets (for the Stack request module) and in AFO 822 – ILL Notice sets (for the Interlibrary Loan module).

Individual documents/templates are pre-defined for each of the notice types via the WordProcessor. These master documents are linked to the notice type (with optional links based on the language) creating a ‘Notice set'.

A Notice set then is linked to specific borrower categories allowing for example one Notice set of master templates used for Adults, and anther set for Staff or Children

Notes

The master notice document can be shared itself across multiple notice sets.

Label and slip printing do not require a notice set, only a single main document.

For request summary listings you can assign various main documents, but there is no notice set.

2.2 Wordprocessing environment[//]

AFO 621 has two menu options that are related to mail merging: “Maintain system printers” and “Print processors maintenance”.

The option “Maintain system printers” leads to a listing of the printers known to the system. There are options to define if the printer is available and the print processors that recognize this printer. Also a printer can be rerouted to a different printer for a specified time interval.

The printers can be added manually. The system cannot “see” the printer – there can be no validation of the input.

Note that the Vubis system does not need direct access to the printer, but simply needs to know that the printer name is a valid one (and hence to be able to offer it as a choice in various contexts).
The name of the printer will be passed to the print processor, directing it to send the results of processing to a printer of that name.

The “Print processors maintenance” option allows the definition and the maintenance of the print processors.

There is also an option to define in AFO 612 for each particular port a default print processor.

3 User interface for printing[//]

If it is known that the notice output is to be sent to the mailmerge processing, then the following output screen is offered:

Screen and printer are mutually exclusive options. Screen means that the system will fully generate the mailmerged document, but will then leave it on screen i.e. in the WP application for the user to look at; it may then be printed or saved.

The File setting may be checked – this indicates that the output is to be saved – it may also be printed, but if the output is to screen, then the results will be saved first.

Printer name will contain all the printers recognized by the current print processor. The current print processor is the print processor defined for the current port in AFO 612. If the current port does not have a print processor assigned than the print processor defined, in AFO 621-Print processors maintenance, as default for the current circulation meta institution will be considered the current print processor.
For label printing from AFO 431 it will default to the printer defined in AFO 481. For label printing from AFO 311 and 354 it will default to the printer defined for the port of the currently logged in user.

Note

For serials labels this screen is not offered.

4 Sending notices by email[//]

Mailmerge processing supports the delivery of the mailmerged letter by email. This is implemented by defining a specific column in the datasource as the field from which the email address may be identified.

This functionality is used by the Vubis mailmerge processing as follows:

· In the SQL view for any type of output, there WILL be a column called EMAIL. This is populated by the Vubis processing to create the relevant SQL table.

· If the required “output” is set to send to printer (that is, as opposed to saving to disk only), then this is interpreted as meaning “send output to printer” if the email field is NOT set; otherwise “send via email” (if the EMAIL field has a valid entry).

· This processing ALWAYS happens. Whether or not the EMAIL field is populated is a function of the application code.

This is a parallel of the existing print functionality in AFO 452, for example – by default, printing notices causes those for which EMAIL is a valid output method to be sent by email, otherwise they are sent to the printer. However, it is also possible to override the sending of notices by email – and to print them instead. This would be paralleled, for mailmerge notices, by simply not putting data into the EMAIL field.

Note

If EMAIL output is required, AND mailmerge notice processing is in force, then the word processing functionality MUST be used. A mix of mailmerged printed notices AND older style notices sent directly by Vubis (via SMTP etc) is NOT supported.

4.1 Security[//]

In order to send mailmerged notices by email from MSOffice, it may be necessary to install an advanced security add-in. By default, later versions of Outlook (the Office email product) will output a warning notice for emails sent from a VB macro. This may be trapped and prevented by installation of the Outlook security add-in.

See http://www.mapilab.com/outlook/security

4.2 Profiles[//]

Email functionality is implemented in different ways according to the MSOffice/OpenOffice implementation – and this must be configured by the end user appropriately. OpenOffice writes directly to a configured SMTP server; MSWord works via Outlook (or variation thereof).

When using MSWord, by default the email output WILL come from the current user's email address. In order to be able to send notices “from” a more generic address, the system calls upon the ability of Outlook to use different “Profiles”. In brief, an Outlook profile is an independent set of parameters to be used when Outlook runs with the specified profile.

From the Microsoft Website -

A profile is what Outlook uses to remember the e-mail accounts and the settings that tell Outlook where your e-mail is stored. A new profile is created automatically when you run Outlook for the first time. The created profile runs whenever you start Outlook. Most people need only one profile. However, sometimes you might find it useful to have more than one profile. For example, you might want one profile for work and another profile for home. Also, if other people use the same computer as you, their accounts and settings can be kept in a separate profile that has a different name from your profile.

Example, create a profile in Outlook for the Vubis email. The Vubis profile may then be configured to use an email address of (for example) TheLibraryService@Mylibrary.org in which case users who reply directly to overdue notices etc. would deliver their replies to this address.

When running from Word , the special macro will attempt to use this profile (and if not found then it will simply use the current settings in Outlook). This email account may be shared by any number of staff users, although it might be appropriate to configure things to make this available as a genuine account to a limited number of staff members whose responsibility it would be to deal with this.

5 Circulation notices[//]

Additional settings in AFO 483 control the circulation notice production.

In AFO 483 – Notices for the options Overdues (1-4), Reservations, Automatic recalls and Manual recalls there is an additional parameter:

This determines whether or not Mailmerge is to be used for the selected notice type.

Note that the Mailmerge option is defined at the printing location level.

In AFO 483 – Mailmerge parameters – Miscellaneous the specific parameters for each type of notice set can be defined.

These govern such settings as Notice location, Sort order, Starting point of new notice, starting point of new file.

All these parameters are set at the printing location level.

6 Notice history[//]

There is a transaction type “Borrower notices” in the transaction history file to record notice output. A full list of notices sent to a borrower is then available from the transaction history listing in AFO 431.

This will record the date generated, and some brief details appropriate to the type of notice - these include:

· Item barcodes

· Date Contextual (due date, reservation held date, invoice date)

· Output format (Printed, email etc)

· Print or reprint 0 or 1.

In addition, a system wide option may be set for mailmerged notices to always save the individual notices. In this case, the system will save each set of notices as a unique file on the shared drive defined in AFO 483 – Mailmerge parameters – Notice sets – Notice type maintenance.

The file name generated will be returned from the mailmerge macro to Vubis, which in turn will add this to the transaction record. If the site chooses to keep their notices (e.g. on a network drive etc.) then it is possible to see the notice actually sent to the borrowers.

The option “View notice” in the online transaction history file will allow the system to attempt to open the document for “notice” transactions in transaction history:

7 Other mailmerge type output[//]

There are also various types of labels that can be printed with mailmerge functionality. These are for borrower cards and for received serials issues. As outlined above this process is slightly different from mailmerge for producing notices.

Besides this, mailmerge can be used to output various stack request listings.

7.1 Borrower labels[//]

It is also possible to use the mailmerge functionality for printing borrower labels from AFO 431. (It does not apply to AFO 432).

This is particularly useful if you want for instance to print a badge including a photo of the borrower.

In the main document template the size of the picture can be defined using the custom properties:

Example of such a label (as printed to screen):

7.2 Serials labels[//]

If the “Print label” field for a subscription is set (under the subscription general details in AFO 321), then the library will have the ability to automatically print labels for serials on receiving serial parts. In addition a manual reprint of the labels is available from the “Modify issues” screen in AFO 311.

With this functionality, the library can manage printing to a label printer. A label printer could print one at a time but this functionality will cater for the situation where the library prints to (say) a LaserJet with Label stationery (e.g. with 5 labels on 1 sheet of paper). In this case, the labels will be “buffered” and sent to the print processor 5 at a time.

To make a virtue out of a necessity, the user could set the “Serials label counter” in AFO 612 to 9999 (say) – then all the labels would be “deferred” and the user can go in AFO 354 to actually output the labels. This then caters for immediate versus deferred type printing.

If the “Serials label counter” in AFO 612 is set to 1 then all the labels will be output at the time of receiving.

Using AFO 354 the library will be able to flush the remaining labels out of the system.

The main document template will need to have the custom property VubisView set to “SerialsLabelView”. The following fields will be available from the SerialsLabelView data source:

· - “regular” bibliographic data

· - from the subscription itself: Institution code, Institution wording, Location code, Location wording, Sublocation code, Sublocation wording, Shelfmark, Subscription no, Alternate subscription no 1, Alternate subscription no 2, Issue enumeration, Issue chronology, Type of holding, Date received, Item barcode (if barcoded)

7.3 Serials slips[//]

It is possible to configure the system to automatically alert the staff when it is time to send received issues out to the bindery. Since the binding frequency of titles can be different, each subscription can have a ‘number of issues to bind' and a ‘delay factor' defined. A delay factor accommodates libraries that do not want to be prompted for binding until the first issue of the next binding unit is received. i.e. you receive 5 issues and then the system tells you to bind the previous 4 issues together.

Issues that are bound are kept together in a binding unit. The system keeps a history of all of the binding units created for each subscription. The library is not required to send issues to the bindery as soon as the binding alert is produced. It is possible to delay sending binding units to the bindery until specific times of the year or until budget requirements are met.

During the serials checkin process, you are alerted of the need to bind by both a message and a printed binding alert. The binding alert contains the issues that should be bound together. After physically locating the issues that are to be bound together, they can be sent out to the bindery along with a printed binding slip.

7.4 Stack request listings[//]

From AFO 813 you can print lists of requests. This is done using the mailmerge functionality. The mailmerge documents (previously defined outside the Vubis system) are made available for this functionality in AFO815 – Request summary print setup.

The input form in AFO 815 allows the user to select a mailmerge master template, and add a suitable description to associate with this layout. With the Print option in AFO 813 the specific layout required can be selected from a dropdown list containing the AFO 815 definitions, at which point the system continues with the standard workflow for mailmerge output.

8 Implementation checklist[//]

When the library wants to use mailmerge functionality the following steps must be taken. They differ depending on type of document.

8.1 For various circulation notices[//]

When the library wants to send one or all these types of notices: Overdues, Reservations (atomatic/manual) Recalls, Courtesy (for overdue items), the following is required:

· AFO 616 – Miscellaneous settings for notice output – Borrower contact methods: define the required contact method on the tabs for the various notice types.

· AFO 483 – Notices - <type of notice>: check the option Mailmerge for the correct type of output.

· AFO 483 – Mailmerge parameters – Miscellaneous: define various parameters related to mailmerging.

· AFO 483 – Mailmerge parameters – Notice sets: define the templates to be used for the various mailmerging documents.

· AFO 483 – Mailmerge parameters – Notice sets by borrower category: define which borrower category will be using which notice set.

· AFO 621 – Print processor maintenance: define at least one print processor.

· AFO 621 – Maintain system printer: define at least one system printer and link this to the print processor as defined above.

· Print processor: at least one PC must be configured to function as print processor. The word processing package will run 'permanently' on this PC. A macro will take care of the mailmerging process. There is a special .ini file (C:\Program Files\VubisSmart\VubisPrintProcessor.ini) associated with this process. This .ini file contains the following information:

- DataSource - The name of the ODBC Datasource used to connect to the Caché server

- Database - The database name (The Caché namespace where Vubis is installed).

- OutlookProfile - The Outlook profile used to send the email notices

- TemporaryWorkArea - The folder/directory location for the temporary storage of data files during processing

- PermanentFileStorage - The folder/directory location for the data that is stored permanently. In practice, this would be a folder on a file server and therefore shared.

- SavedOutputSetsStorage - The folder/directory location where the individual output sets are stored if this option was chosen for a particular type of notice .

8.2 For courtesy notices expiring membership[//]

When the library wants to send notices to users informing them of their expiring membership, the following is required:

· AFO 481 – Membership related – Expiring membership notification: define the number of days prior to expiry date the notice must be sent.

· AFO 616 – Miscellaneous settings for notice output – Borrower contact methods: define the required contact method on the tab "Membership".

· AFO 483 – Mailmerge parameters – Miscellaneous: define various parameters related to mailmerging.

· AFO 483 – Mailmerge parameters – Notice sets: define a template to be used for the mailmerging document.